﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Data.Sqlite;

namespace DefensiveProgrammingApp.Services
{
    public class DataService
    {
        private readonly string connectionString;

        public DataService(string databasePath)
        {
            connectionString = $"Data Source={databasePath}";
        }

        /// <summary>
        /// 查询数据
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        public string RetrieveData(string key)
        {
            using (var connection = new SqliteConnection(connectionString))
            {
                connection.Open();

                using (var command = connection.CreateCommand())
                {
                    command.CommandText = "SELECT name FROM appTab WHERE nickname = @key";
                    command.Parameters.AddWithValue("@key", key);

                    var result = command.ExecuteScalar();

                    connection.Close();

                    return result?.ToString();
                }
            }
        }


        /// <summary>
        /// 删除一条数据
        /// </summary>
        /// <param name="key"></param>
        public bool DeleteData(string key)
        {
            bool success = false;

            using (var connection = new SqliteConnection(connectionString))
            {
                connection.Open();

                // 先检查记录是否存在
                using (var checkCommand = connection.CreateCommand())
                {
                    checkCommand.CommandText = "SELECT COUNT(*) FROM appTab WHERE nickname = @key";
                    checkCommand.Parameters.AddWithValue("@key", key);

                    int count = Convert.ToInt32(checkCommand.ExecuteScalar());

                    if (count > 0)
                    {
                        // 记录存在，执行删除操作
                        using (var deleteCommand = connection.CreateCommand())
                        {
                            deleteCommand.CommandText = "DELETE FROM appTab WHERE nickname = @key";
                            deleteCommand.Parameters.AddWithValue("@key", key);

                            deleteCommand.ExecuteNonQuery();
                            success = true;
                        }
                    }
                    
                }

                connection.Close();
            }

            return success;
        }



        /// <summary>
        /// 插入或更新
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        public bool InsertOrUpdateData(string key, string value)
        {
            bool success = false;
            using (var connection = new SqliteConnection(connectionString))
            {
                connection.Open();

                // 先检查记录是否存在
                using (var checkCommand = connection.CreateCommand())
                {
                    checkCommand.CommandText = "SELECT COUNT(*) FROM appTab WHERE nickname = @key";
                    // SELECT * FROM appTab WHERE nickname = 'qwer'
                    checkCommand.Parameters.AddWithValue("@key", key);

                    int count = Convert.ToInt32(checkCommand.ExecuteScalar());

                    if (count > 0)
                    {
                        // 记录已存在，执行更新操作
                        using (var updateCommand = connection.CreateCommand())
                        {
                            updateCommand.CommandText = "UPDATE appTab SET name = @value WHERE nickname = @key";
                            updateCommand.Parameters.AddWithValue("@key", key);
                            updateCommand.Parameters.AddWithValue("@value", value);

                            updateCommand.ExecuteNonQuery();
                            success = true;
                        }
                    }
                    else
                    {
                        // 记录不存在，执行插入操作
                        using (var insertCommand = connection.CreateCommand())
                        {
                            insertCommand.CommandText = "INSERT INTO appTab (nickname, name) VALUES (@key, @value)";
                            // INSERT INTO appTab (nickname, name) VALUES ('wasd', '科室id')
                            insertCommand.Parameters.AddWithValue("@key", key);
                            insertCommand.Parameters.AddWithValue("@value", value);

                            insertCommand.ExecuteNonQuery();
                            success = true;
                        }
                    }
                }

                connection.Close();
            }

            return success;
        }


    }
}
